package com.snsoft.util;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * 
 * @author 慎伟康
 * 
 * @date 2017年9月17日 上午7:11:22
 * 
 * @Description TODO
 *		jdbc工具类
 *    jar依赖：
 *    mysql-connector-java-5.1.7-bin.jar
 *    c3p0-0.9.1.2.jar
 */
public final class JdbcUtils {
	
    private static ComboPooledDataSource dataSource;
    /** 线程级别变量，线程安全的，可用于事务    */
    private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
	
    /** 加载数据源    */
	static{
		dataSource = new ComboPooledDataSource();
		//设置四个基本属性
		try {
			dataSource.setDriverClass("com.mysql.jdbc.Driver");
			dataSource.setJdbcUrl("jdbc:mysql://180.76.138.110:3306/ghcontrol?useUnicode=true&characterEncoding=utf-8&useOldAliasMetadataBehavior=true");
			dataSource.setUser("root");
			dataSource.setPassword("syauxdxy");
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 取得数据源
	 * @return
	 */
	public static ComboPooledDataSource getDataSource() {
		return dataSource;
	}
	
	/**
	 * 取得连接
	 * @return
	 * @throws SQLException
	 */
	public static Connection getMySQlConnection(){
		//返回当前线程绑定的连接
		Connection conn = tl.get();
		
		//如果线程未绑定连接对象
		if(conn == null){
			//取得连接池的一个空闲连接对象
			try {
				conn = dataSource.getConnection();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
			//绑定到当前线程
			tl.set(conn);
		}
		return conn;
	}
	
	/**
	 * 释放资源
	 * @param conn
	 * @throws SQLException
	 */
	public static void close(Connection conn){
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
			conn = null;
		}
	}
	public static void close(Statement stat){
		if(stat != null){
			try {
				stat.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
			stat = null;
		}
	}
	public static void close(ResultSet rs){
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
			rs = null;
		}
	}
	public static void close(Connection conn, Statement stat, ResultSet rs){
		close(rs);
		close(stat);
		close(conn);
	}
	public static void close(Connection conn, Statement stat){
		close(stat);
		close(conn);
	}
	
	/**
	 * 开启事务
	 * @throws SQLException 
	 */
	public static void startTransaction() {
		Connection conn = getMySQlConnection();
		if(conn != null){
			try {
				conn.setAutoCommit(false);
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}
	
	/**
	 * 事务回滚
	 * @throws SQLException 
	 */
	public static void rollback() {
		Connection conn = getMySQlConnection();
        if(conn != null){
        	try {
				conn.rollback();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}
	
	/**
	 * 事务提交
	 * @throws SQLException 
	 */
	public static void commit() {
		Connection conn = getMySQlConnection();
		if(conn != null){
			try {
				conn.commit();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}
	
	/**
	 * 解除当前线程的绑定，关闭连接
	 * @throws SQLException
	 */
	public static void closeConnection() {
		Connection conn = getMySQlConnection();
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
			tl.remove();
		}
	}
	
	/**
	 * 处理结果集，获取列表
	 * @param rs
	 * @return List
	 */
	public static List<HashMap<String, Object>> getList(ResultSet rs) {
		List<HashMap<String, Object>> result = new ArrayList<HashMap<String,Object>>();
		HashMap<String, Object> temp = null;
		ResultSetMetaData rsmd = null;
		try {
			rsmd = rs.getMetaData();
			while(rs.next()){
				temp = new HashMap<String, Object>();
				for(int i = 1; i <= rsmd.getColumnCount(); i++){
					temp.put(rsmd.getColumnName(i), rs.getObject(i));
				}
				result.add(temp);
			}	
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		if(result == null || result.size() == 0){
			return null;
		}else{
			return result;
		}
	}
	
	/**
	 * 获取一条记录
	 * @param rs
	 * @return Map
	 */
	public static HashMap<String, Object> getHashMap(ResultSet rs) {
		List<HashMap<String, Object>> result = new ArrayList<HashMap<String,Object>>();
		result = getList(rs);
		if(result == null || result.size() == 0){
			return null;
		}else{
			return result.get(0);
		}
	}
}

